﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using BLL;

namespace DAL
{
    public class OperateDBManage: IOperateDBRepository
    {
        private OperateDB opDB = new OperateDB("Data Source=cntsnwa025737\\sql2008r2;Initial Catalog=TrainingWorkShop;Integrated Security=True ");//创建db对象
        //opDB是用这个类定义的对象，可以调用operateDB类里函数



        public List<Authority> GetAuthority()
        {

            string sql = "select AuthorityId,AuthorityName from Authority";
            
            DataTable AuthoritySelect = OperateDB.GetFillData(sql);
            List<Authority> authorityList = new List<Authority>(); //
            foreach (DataRow dr in AuthoritySelect.Rows)
            {
                Authority authority = new Authority();
                authority.AuthorityId = (int)dr["AuthorityId"];
                authority.AuthorityName = (string)dr["AuthorityName"];
                
                authorityList.Add(authority);

            }
            return authorityList;

            //return opDB.Select("select * from Authority");
        }


       //public SqlDataReader GetRole(int role_temp)
       //{
       //    string select_RoleId = "select RoleId from Role where RoleId='" + role_temp + "'";//语句需要执行
           
       //   return opDB.Select(select_RoleId);
  
       //}

        public List<Role> GetRole(int role_temp)
        {
            string select_RoleId = "select RoleId from Role where RoleId='" + role_temp + "'";
            DataTable RoleSelect = OperateDB.GetFillData(select_RoleId);
            List<Role> roleList = new List<Role>(); //
            foreach (DataRow dr in RoleSelect.Rows)
            {
                Role role = new Role();
                role.RoleId = (int)dr["RoleId"];
                roleList.Add(role);
            }
            return roleList;
        }

        public object GetStatus(int role_temp)
        {
            string select_statusid = "select StatusId from Role where RoleId='" + role_temp + "'";
            return opDB.SelectS(select_statusid);           
        }

        public object GetRoleName(int role_temp)
        {
            string select_rolename = "select RoleName from Role where RoleId='" + role_temp + "'";
            return opDB.SelectS(select_rolename);
            //return new SqlCommand(select_rolename, opDB.DbConnectionProperty).ExecuteScalar();
        }

        ////public SqlDataReader GetAuthorityid(int role_temp) //定义一个返回值是sqldatareader的函数
        ////{
        ////    string select_authorityid = "select AuthorityId from RoleAuthority where RoleId='" + role_temp + "'";
        ////    return opDB.Select(select_authorityid); //           
        ////}

        public List<RoleAuthority> GetAuthorityid(int role_temp)
        {
            string selectAuthorityId = "select AuthorityId from RoleAuthority where RoleId='" + role_temp + "'";
            DataTable AuthorityIdSelect = OperateDB.GetFillData(selectAuthorityId);
            List<RoleAuthority> authorityList = new List<RoleAuthority>(); //
            foreach (DataRow dr in AuthorityIdSelect.Rows)
            {
                RoleAuthority authority = new RoleAuthority();
                authority.AuthorityId = (int)dr["AuthorityId"];
                authorityList.Add(authority);
            }
            return authorityList;
        }
        ////public List<RoleAuthority> GetAuthorityid(int role_temp)
        ////{
        ////    string selectAuthorityId = "select AuthorityId from RoleAuthority where RoleId='" + role_temp + "'";
        ////    DataTable AuthorityIdSelect = OperateDB.GetFillData(selectAuthorityId);
        ////    List<RoleAuthority> authorityList = new List<RoleAuthority>(); //
        ////    foreach (DataRow dr in AuthorityIdSelect.Rows)
        ////    {
        ////        RoleAuthority roleauthority = new RoleAuthority();
        ////        roleauthority.AuthorityId1 = (int)dr["AuthorityId"];
        ////        authorityList.Add(roleauthority);
        ////    }
        ////    return authorityList;
        ////}

        ////public void GetUpdateRoleName(string TextBox, int role_temp)
        ////{
        ////    string update_rolename = "update Role set RoleName='" + TextBox + "' where RoleId=" + role_temp + "";
        ////    opDB.Execute(update_rolename);
           
        ////}

        //public SqlDataReader GetNewStatusid()
        //{
        //    string new_StatusId = "select StatusId from StatusText where statusName='In Use'";//根据statusname查询statusid
        //    return opDB.Select(new_StatusId);
        //}
        public int GetNewStatusid()
        {
            
            string new_StatusId = "select StatusId from StatusText where statusName='in use'";//根据statusname查询statusid
            DataTable StatusIdSelect = OperateDB.GetFillData(new_StatusId);
            int statusidsearch = 0;
            foreach (DataRow dr in StatusIdSelect.Rows)
            {

                statusidsearch = (int)dr["StatusId"];
            }

            return statusidsearch;
            
        }



        public void UpdateStatusId(int new_StatusidSelect, int role_temp)
        {
            string update_StatusId = "update Role set StatusId=" + new_StatusidSelect + " where RoleId=" + role_temp + "";
            opDB.Execute(update_StatusId);
        }

        public void DeleteAuthorityId(int role_temp)
        {
            string delete_RoleAuthority = "delete from RoleAuthority where RoleId=" + role_temp + "";
            opDB.Execute(delete_RoleAuthority);
        }

        public void InsertRoleAuthority(string str, int role_temp)
        {
            string insert_RoleAuthority = "insert into RoleAuthority(RoleId,AuthorityId) values('" + role_temp + "','" + str + "')";
            opDB.Execute(insert_RoleAuthority);
        }

        //public SqlDataReader GetNewStatusid1()
        //{
        //    string new_StatusId = "select StatusId from StatusText where statusName='No Use'";//根据statusname查询statusid
        //    return opDB.Select(new_StatusId);
        //}
        public int GetNewStatusid1()
        {

            string new_StatusId = "select StatusId from StatusText where statusName='no use'";//根据statusname查询statusid
            DataTable StatusIdSelect = OperateDB.GetFillData(new_StatusId);
            int statusidsearch = 0;
            foreach (DataRow dr in StatusIdSelect.Rows)
            {
                
                statusidsearch = (int)dr["StatusId"];
            }

            return statusidsearch;

        }

        public void InsertRoleAuthority(int StatusId_Select, string TextBox)
        {
            string insert_SelectStatusId = "insert into role(StatusId,RoleName) values(" + StatusId_Select + ",'" + TextBox + "')";//把查询出来的statusid插到role表格
            opDB.Execute(insert_SelectStatusId);
        }

        public object GetRoleId(string TextBox1)
        {
            string select_RoleId = "select RoleId from Role where RoleName='" + TextBox1 + "'";
            return opDB.SelectS(select_RoleId);
        }

        public string SelectRoleIdInput(string TextBox_input)
        {
            string select_RoleIdInput = "select RoleId from Role where RoleId='" + TextBox_input + "'";
            return opDB.SelectS(select_RoleIdInput);
        }


    }
}
